package com.event.usc.background;

import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import oracle.jdbc.OracleConnection;

public class DatabaseHandler {
	private static final String HOST = "128.125.163.168";
	private static final String PORT = "1521";
	private static final String USERNAME = "team15";   
	private static final String PASSWORD = "scevt";
	private static final String DBNAME = "csci585";
	private static final String URL = "jdbc:oracle:thin:@";

	protected OracleConnection connection;

	/**
	 * 
	 */
	public DatabaseHandler() {
		String url = URL + HOST + ":" + PORT + ":" + DBNAME;;
		try {
			DriverManager.registerDriver(new oracle.jdbc.OracleDriver());
			connection = (OracleConnection) DriverManager.getConnection(url,
					USERNAME, PASSWORD);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
	}
	
	public void closeConnection() {
		if (this.connection != null) {
			try {
				this.connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
	public boolean isConnected() {
		return this.connection == null;
	}
	
	public boolean insertRecordToDB(List<Event> events) {
		if (this.connection == null) {
			return false;
		}
		String sqlStmt = "INSERT INTO EVENTS SELECT eventid_seq.nextval,?,?,?,?,?,?,?,?,? FROM DUAL WHERE EXISTS (SELECT LOCATION_CODE FROM LOCATIONS WHERE LOCATION_CODE=?)";
		try {
			PreparedStatement pstmt = this.connection.prepareStatement(sqlStmt);
			for (Event event:events)
			{
				pstmt.setString(1, event.getEventTitle() );
				String sum = event.getEventSum();
				if (sum.length()>3000)
					sum = sum.substring(0, 3000);
				String des = event.getEventDes();
				if (des!=null)
				{
					if (des.length()>3000)
					des = des.substring(0, 3000);
				}
				String doc = event.getEventCat() + " " + event.getEventTitle() + " " + sum + " " + des;
				if (doc.length()>3000)
					doc = doc.substring(0, 3000);
				pstmt.setString(2, sum);
				pstmt.setString(3, des);
				pstmt.setString(4, event.getEventImg());
				pstmt.setLong(5, event.getEventStart());
				pstmt.setLong(6, event.getEventEnd());
				pstmt.setString(7, event.getEventLoc());
				pstmt.setString(8, event.getEventCat());
				pstmt.setString(9, doc);
				pstmt.setString(10, event.getEventLoc());
				pstmt.execute();
			}
			pstmt.close();
			return true;
		}catch (SQLException ex) {
			ex.printStackTrace();
			return false;
		}
	}
}

